package com.excel.operations;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.Date;
import java.util.List;

import org.apache.commons.beanutils.PropertyUtils;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

public class ExcelCreator {
	
	private Workbook workbook;
	private String fileName; 
	
	public ExcelCreator(String fileName){
		this.workbook = ExcelUtils.getNewWorkBook(fileName);
		this.fileName = fileName;
	}
	
	public void create() throws IOException{
		// Create an output stream to write the report to.
        OutputStream output = new FileOutputStream(fileName);

        // Write the report to the output stream
        workbook.write(output);

        // Finally, save the report
        output.close();
	}
	
    public void addSheet(List<?> data, ReportColumn[] columns, String sheetName) {
    	 
        Sheet sheet = workbook.createSheet(sheetName);
        int numCols = columns.length;
        int currentRow = 0;
        Row row;
        
        Font boldFont = workbook.createFont();
        boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        
        try {
 
            // Create the report header at row 0
            row = sheet.createRow(currentRow);
            // Loop over all the column beans and populate the report headers
            for (int i = 0; i < numCols; i++) {
                // Get the header text from the bean and write it to the cell
                writeCell(row, i, columns[i].getHeader(), FormatType.TEXT);
            }
 
            currentRow++; // increment the spreadsheet row before we step into
                            // the data
 
            // Write report rows
            for (int i = 0; i < data.size(); i++) {
                // create a row in the spreadsheet
                row = sheet.createRow(currentRow++);
                // get the bean for the current row
                Object bean = data.get(i);
                // For each column object, create a column on the current row
                for (int y = 0; y < numCols; y++) {
                    Object value = PropertyUtils.getProperty(bean,
                            columns[y].getMethod());
                    writeCell(row, y, value, columns[y].getType());
                }
            }
 
            // Autosize columns
            for (int i = 0; i < numCols; i++) {
                sheet.autoSizeColumn((short) i);
            }
 
        } catch (Exception e) {
            System.err.println("Caught Generate Error exception: "
                    + e.getMessage());
        }
 
    }
    
    private CellStyle getCellStyle(String format){
		CreationHelper createHelper = workbook.getCreationHelper();
		CellStyle cellStyle = workbook.createCellStyle();
		cellStyle.setDataFormat(createHelper.createDataFormat().getFormat(format));
		return cellStyle;
	}
    
    private void writeCell(Row row, int col, Object value, FormatType formatType){
    	Cell cell = row.createCell(col);
    	if (value == null) {
            return;
        }
    	switch (formatType) {
        case TEXT:
            cell.setCellValue(value.toString());
            break;
        case INTEGER:
            cell.setCellValue(((Number) value).intValue());
            cell.setCellStyle(getCellStyle("#,##0"));
            break;
        case FLOAT:
            cell.setCellValue(((Number) value).doubleValue());
            cell.setCellStyle(getCellStyle("#,##0.00"));
            break;
        case DATE:
            cell.setCellValue((Date) value);
            cell.setCellStyle(getCellStyle("m/d/yy"));
            break;
        case MONEY:
            cell.setCellValue(((Number) value).intValue());
            cell.setCellStyle(getCellStyle("$#,##0.00;$#,##0.00"));
            break;
        case PERCENTAGE:
            cell.setCellValue(((Number) value).doubleValue());
            cell.setCellStyle(getCellStyle("0.00%"));
        }
    }
}
